package RPPM.dao.impl;

import RPPM.dao.OnsitestaffDao;
import RPPM.domain.Onsitestaff;
import RPPM.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

public class OnsitestaffDaoImpl implements OnsitestaffDao {
    private JdbcTemplate jdbcTemplate =new JdbcTemplate(JDBCUtils.getDataSource());
    /**
     * 登录
     */
    @Override
    public Onsitestaff findOnsitestaffById(Onsitestaff tobechecked) {

        String sql = "select * from onsitestaff where Staff_id = ?";
        Onsitestaff onsitestaff = null;
        try {

            onsitestaff = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Onsitestaff>(Onsitestaff .class), tobechecked.getStaff_id());

        } catch (Exception e){
            onsitestaff = null;
            return onsitestaff;
        }
        return onsitestaff;
    }
    /**
     * 找寻当前课程信息
     */
    @Override
    public List<String> findCourseMessage(int Cou_id) {

        String sql = "select * from course where Cou_id = '"+Cou_id+"'";
        List<String> course = null;
        try {

            course = jdbcTemplate.queryForList(sql, String.class);

        } catch (Exception e){
            course = null;
            return course;
        }
        return course;
    }

    @Override
    public List<String> getCourses() {
        String sql = "select Cou_name from course";
        List<String> courses = null;
        try {
            courses = jdbcTemplate.queryForList(sql,String.class);
        } catch (Exception e){
            return courses;
        }
        return courses;
    }

    @Override
    public List<Map<String, Object>> getStudentsByCouname(String cou_name) {
        String sql = "select student.Stu_name, student.Stu_sex, student.Stu_level, student.Stu_company, student.Stu_post, student.Stu_phone from student, course_student where course_student.Cou_name = ? and course_student.Stu_id = student.Stu_id";
        try{
            List<Map<String, Object>> courses;
            courses = jdbcTemplate.queryForList(sql, cou_name);
            return courses;
        } catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Map<String, Object>> getStudentsByStuname(String cou_name, String stu_name) {
        String sql = "select student.Stu_name, student.Stu_sex, student.Stu_level, student.Stu_company, student.Stu_post, student.Stu_phone from student, course_student where course_student.Cou_name = ? and course_student.Stu_id = student.Stu_id and student.Stu_name like ?";
        try{
            List<Map<String, Object>> courses;
            courses = jdbcTemplate.queryForList(sql, cou_name, "%"+stu_name+"%");
            return courses;
        } catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public void postSign(String cou_name) {
        String sql = "update course_student set Stu_sign = 0 where Cou_name = ? and Stu_sign = 2";
        try{
            jdbcTemplate.update(sql, cou_name);
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void postAdvice(String cou_name) {
        String sql = "update course_student set Cou_advice = 0 where Cou_name = ? and Cou_advice = 2";
        try{
            jdbcTemplate.update(sql, cou_name);
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public List<Map<String, Object>> getStatisticsSign(String cou_name) {
        String sql = "select course_student.Stu_name, course_student.Cou_name, course.Cou_time, course.Cou_teacher, course_student.Stu_sign from course_student, course where course.cou_id = course_student.Cou_id and course_student.Cou_name = ?";
        try{
            List<Map<String, Object>> signs;
            signs = jdbcTemplate.queryForList(sql, cou_name);
            System.out.println(signs);
            return signs;
        } catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<Map<String, Object>> getStatisticsAdvice(String cou_name) {
        String sql = "select course_student.Stu_name, course_student.Cou_name , course.Cou_teacher, course_student.Cou_advice from course_student, course where course_student.Cou_name = ? and course.Cou_name = course_student.Cou_name and Cou_advice <> 2";
        try{
            List<Map<String, Object>> signs;
            signs = jdbcTemplate.queryForList(sql, cou_name);
            System.out.println(signs);
            return signs;
        } catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

}
